const { TaskAssignment, WorkOrder, Employee, Department } = require('../../database/index');
const sequelize = require('sequelize');
const { getExecutedSql } = require('../../logger');

async function performDatabaseQuery (startDate, endDate, employeeName)
{
    let dateRange
    if (startDate && endDate) {
        dateRange = [startDate, endDate]; // 根据外部传入的开始日期和结束日期构建一个数组
    }
    const results = await TaskAssignment.findAll({
        attributes: [
            [
                sequelize.Sequelize.literal("DATE_FORMAT(workOrder.CreatedDate, '%Y-%m-%d')"),
                'WeekStart',
            ],
            [
                sequelize.Sequelize.literal("DATE_FORMAT(workOrder.CreatedDate, '%Y-%m-01')"),
                'MonthStart',
            ],
            [
                sequelize.fn('COUNT', sequelize.Sequelize.col('workOrder.WorkOrderName')),
                'WeeklyCompletedOrders',
            ],
            [
                sequelize.fn('COUNT', sequelize.Sequelize.col('workOrder.WorkOrderName')),
                'MonthlyCompletedOrders',
            ]
        ],
        include: [
            {
                model: Employee,
                as: 'employee',
                attributes: ['Name', 'DepartmentID'],
            },
            {
                model: WorkOrder,
                as: 'workOrder',
                attributes: ['WorkOrderName', 'Income', 'CreatedDate'],
            },
        ],
        group: [
            'employee.Name',
            'employee.DepartmentID',
            sequelize.Sequelize.literal("DATE_FORMAT(workOrder.CreatedDate, '%Y-%m-%d')"),
            sequelize.Sequelize.literal("DATE_FORMAT(workOrder.CreatedDate, '%Y-%m-01')"),
        ],
        raw: true,
        where: {
            // 在这里添加日期范围条件
            ...(dateRange && {
                '$workOrder.CreatedDate$': {
                    [sequelize.Op.between]: dateRange,
                },
            }),
            // 在这里添加按姓名搜索条件
            ...(employeeName && {
                '$employee.Name$': employeeName,
            })
        },
    });
    return results;
};
async function getDepartmentName (departmentId)
{
    const department = await Department.findOne({ where: { DepartmentID: departmentId } }); // 查询部门模型中ID匹配的部门对象
    console.log(department);
    if (department) {
        return department.dataValues.DepartmentName; // 返回部门名称
    }
    return null; // 如果找不到部门，则返回null或其他适当的值
}

module.exports = { performDatabaseQuery, getDepartmentName }